<?php
	class sqlList
	{
		public $SelectSongDetail = "SELECT `title`, `content`, `music`, `lyrics`, `youtubeCode`, `album` FROM `poem` WHERE `idPoem` = ?;";
    public $SelectSongCategory = "SELECT `category`.`idCategory`, `category`.`categoryName` FROM `category` INNER JOIN `poem_cat` ON `poem_cat`.`idCategory` = `category`.`idCategory` WHERE `poem_cat`.`idPoem` = ?;";
    public $SelectAllSong = "SELECT `idPoem`, `title` FROM `poem`;";
    public $SelectCategory = "SELECT P.`idPoem`, P.`title` FROM `poem` as P INNER JOIN `poem_cat` ON `poem_cat`.`idPoem` = P.`idPoem` WHERE `poem_cat`.`idCategory` = ? ;";
    public $SelectAllCategory = "SELECT `category`.`idCategory`, `category`.`categoryName` FROM `category`";
    public $SelectAllAlbum = "SELECT album.idAlbum, album.albumName, count(pa.idPoem) as numOfPoem  FROM album as album inner join poem_album as pa on pa.idAlbum = album.idAlbum group by pa.idAlbum;";
    public $SelectAlbum = "select poem.idPoem, poem.title from poem as poem  inner join poem_album as pa on pa.idPoem = poem.idPoem inner join album as album on album.idAlbum = pa.idAlbum where album.idAlbum = ?;";
    public $UpdatePoemById = "UPDATE poem set `title` = ?, `content` = ?, `music` = ?, `lyrics` = ?, `youtubeCode` = ? WHERE `idPoem` = ?;";


	  public $InsertPhotoStatement = "INSERT INTO Photo (`photoId`,`userid`, `fileName`, `path`,`uploadTime`,`categoryid`,`size`, `remark1`) VALUES (?,?,?,?,Now(),?,?,?)";
		public $queryImagePrefix = "SELECT COUNT(*)+1 FROM Photo WHERE categoryid=?";
		public $queryUploadImageByAccount = "SELECT Photo.photoid, Photo.path, Photo.uploadTime, Photo.isPaid, Photo.size, Category.categoryName FROM Photo INNER JOIN Category ON Photo.categoryid = Category.categoryid WHERE userid=? order by Photo.uploadTime desc";
	}
?>